package com.bosch.in.rtp1.ept.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

/**
 * Base DAO class which provides common functionalities for every concrete DAO implementation class to reuse.
 * @author TPR1HC
 *
 */
class GenericDao {

	/**
	 * Database connection
	 */
	protected Connection connection;
	/**
	 * prepare statement to execute CRUD action
	 */
	protected PreparedStatement preStatement;
	
	private static final Logger LOGGER = Logger.getLogger(GenericDao.class);
	
	/**
	 * Result set
	 */
	protected ResultSet rs;
	
	/**
	 * Constructor to inject connection
	 * @param connection	DB connection
	 */
	public GenericDao(Connection connection) {
		this.connection = connection;
	}
	
	/**
	 * build prepare statement and execute it
	 * @param sqlStatement		query statement
	 * @param parameterValues	parameter list which can be null
	 * @return result set 
	 * @throws SQLException
	 */
	protected ResultSet executeQuery(String sqlStatement, Object[] parameterValues) throws SQLException {
		buildPrepareStatement(sqlStatement, parameterValues);
		printQuert(sqlStatement, parameterValues);
		rs = preStatement.executeQuery();
		return rs;
	}
	
	/**
	 * 
	 * @param sqlStatement
	 * @param parameterValues
	 * @return
	 * @throws SQLException
	 */
	protected boolean executeUpdate(String sqlStatement, Object[] parameterValues) throws SQLException {
		buildPrepareStatement(sqlStatement, parameterValues);
		printQuert(sqlStatement, parameterValues);
		preStatement.executeUpdate();
		preStatement.close();
		return true;
	}
	
	/**
	 * 
	 * @param sqlStatement
	 * @param parameterValues
	 * @return
	 * @throws SQLException
	 */
	protected boolean executeBatchUpdate(String sqlStatement, Object[][] parameterValues) throws SQLException {
		preStatement = connection.prepareStatement(sqlStatement);
		for (int i = 0; i < parameterValues.length; i++) {
			addParameter(parameterValues[i]);
			preStatement.addBatch();
		}
		printQuert(sqlStatement, parameterValues);
		preStatement.executeBatch();
		preStatement.close();
		return true;
	}
	
	/**
	 * 
	 * @param sqlStatement
	 * @param parameterValues
	 * @return
	 * @throws SQLException
	 */
	protected int executeInsert(String sqlStatement, Object[] parameterValues) throws SQLException {
		buildPrepareStatement(sqlStatement, parameterValues);
		printQuert(sqlStatement, parameterValues);
		preStatement.executeUpdate();
		preStatement.close();
		return 1;
	}
	
	/**
	 * 
	 * @param sqlStatement
	 * @param parameterValues
	 * @return
	 * @throws SQLException
	 */
	protected boolean executeDelete(String sqlStatement, Object[] parameterValues) throws SQLException {
		buildPrepareStatement(sqlStatement, parameterValues);
		printQuert(sqlStatement, parameterValues);
		preStatement.executeUpdate();
		preStatement.close();
		return true;
	}
	
	/**
	 * close prepare statement after its execution.
	 * @throws SQLException
	 */
	protected void close() throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (preStatement != null) {
			preStatement.close();
		}
	}
	
	/**
	 * build prepared statement which includes all given parameters.
	 * @param sqlStatement		query statement
	 * @param parameterValues	parameter list which can be null
	 * @return	prepared statement
	 * @throws SQLException
	 */
	private PreparedStatement buildPrepareStatement(String sqlStatement, Object[] parameterValues) throws SQLException {
		preStatement = connection.prepareStatement(sqlStatement);
		addParameter(parameterValues);
		
		return preStatement;
	}

	/**
	 * Add parameter to prepared statement
	 * @param parameterValues	list of parameter values
	 * @throws SQLException
	 */
	private void addParameter(Object[] parameterValues) throws SQLException {
		if (parameterValues != null && parameterValues.length > 0) {
			for (int i = 0; i < parameterValues.length; i++) {
				Object parameterValue = parameterValues[i];
				if (parameterValue instanceof String) { // parameter in String type
					preStatement.setString(i + 1, (String) parameterValue);
				} else if (parameterValue instanceof Integer) { // parameter in Integer type
					preStatement.setInt(i + 1, (Integer) parameterValue);
				} else if (parameterValue instanceof Date) {
					preStatement.setDate(i + 1, (Date) parameterValue);
				} else { 
					preStatement.setObject(i + 1, parameterValue);
				}
			}
		}
	}
	
	/**
	 * Export log for sql statement and also replace place holders by real parameter values
	 * @param strSql
	 * @param collectionParams
	 */
	private static void printQuert(String strSql, Object[] collectionParams)
    {
		LOGGER.info("--Before..." + strSql);
        if (collectionParams != null)
        {
            for (int i = 0; i < collectionParams.length; i++)
            {
            	String params = collectionParams[i] == null ? "null" : "'"+collectionParams[i].toString()+"'";
                strSql = StringUtils.replaceOnce(strSql, "?",  params );
            	LOGGER.debug("--Parameters-->" + collectionParams[i]);
            }
        }
        LOGGER.info("--Query After...\n" + strSql + ";\n/");
        LOGGER.info("--________________________________________________________________________________________________________________________________________");
    }

}
